﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data;
using System.IO;

namespace Project_02
{
    public struct DataInfo {
        private String name;
        private Int32  id;
        public DataInfo(String name, Int32 id) {
            this.name = name;
            this.id = id;
        }
        public String Name {
            get { return name; }
        }
        public Int32 ID {
            get { return id; }
        }
    }
    public class User {
        private List<DataInfo> user;
        public User(List<DataInfo> user) {
            this.user = user;
        }
        public String GetUser(Int32 ID) {
            foreach (DataInfo user in this.user) {
                if (ID == user.ID) return user.Name;
            }
            throw new Exception("There is no user associated with the ID " + ID + "!");
        }
        public List<DataInfo> List {
            get { return user; }
        }
        public Int32 GetID(String user) {
            foreach (DataInfo data in this.user) {
                if (data.Name == user) return data.ID;
            }
            throw new Exception("There is no user named " + user + "!");
        }
    }
    public class Rank {
        private List<DataInfo> rank;
        public Rank(List<DataInfo> rank) {
            this.rank = rank;
        }
        public String GetRank(Int32 ID) {
            foreach (DataInfo rank in this.rank) {
                if (ID == rank.ID) return rank.Name;
            }
            return "<unknown rank>";
        }
        public List<DataInfo> List {
            get { return rank; }
        }
        public Int32 Compare(String a, String b) {
            return GetID(a) - GetID(b);
        }
        public Int32 GetID(String rank) {
            foreach (DataInfo data in this.rank) {
                if (data.Name == rank) return data.ID;
            }
            throw new Exception("There is no rank named " + rank + "!");
        }
    }
    public class Relation {
        private List<DataInfo> relation;
        public Relation(List<DataInfo> relation) {
            this.relation = relation;
        }
        public String GetRelation(Int32 ID) {
            foreach (DataInfo relation in this.relation) {
                if (ID == relation.ID) return relation.Name;
            }
            return "<unknown relation>";
        }
        public List<DataInfo> List {
            get { return relation; }
        }
        public Int32 GetID(String relation) {
            foreach (DataInfo data in this.relation) {
                if (data.Name == relation) return data.ID;
            }
            throw new Exception("There is no relation named " + relation + "!");
        }
    }
    public class Gender {
        private List<DataInfo> gender;
        public Gender(List<DataInfo> gender) {
            this.gender = gender;
        }
        public String GetRank(Int32 ID) {
            foreach (DataInfo gender in this.gender) {
                if (ID == gender.ID) return gender.Name;
            }
            return "<unknown gender>";
        }
        public List<DataInfo> List {
            get { return gender; }
        }
    }
    public class State {
        private List<DataInfo> state;
        public State(List<DataInfo> state) {
            this.state = state;
        }
        public String GetRank(Int32 ID) {
            foreach (DataInfo state in this.state) {
                if (ID == state.ID) return state.Name;
            }
            return "<unknown state>";
        }
        public List<DataInfo> List {
            get { return state; }
        }
    }
    public class Database
    {
        //sqlcmd -S Botika-PC\CRSSQLSERVER
        //botika-pc\crssqlserver
        //Botika-PC\\CRSSQLSERVER
        //127.0.0.1\\CRSSQLSERVER
        private SqlConnection connection;
        private DataInfo      admin;
        private User          user;
        private Rank          rank;
        private Relation      relation;
        private Gender        gender;
        private State         state;
        public Database(String server, String username, String password, Int32 timeout) {
            //String server = "Botika-PC\\CRSSQLSERVER";
            //server = "127.0.0.1\\CRSSQLSERVER";
            connection = new SqlConnection("User ID=" + username + ";" + 
                                           "Password=" + password + ";" +
                                           "Data Source=" + server + ";" + 
                                           "Trusted_Connection=false;" + 
                                           "Initial Catalog=Messenger;" + 
                                           "Integrated Security=false;" + 
                                           "MultipleActiveResultSets=True;" + 
                                           "connection timeout=" + timeout.ToString());
            if (!IsUserAllowed(username)) throw new Exception(username + " does not have moderator or administrator rights!");
            this.rank     = GetRank();
            this.gender   = GetGender();
            this.state    = GetState();
            this.user     = GetUser(username, false);
            this.relation = GetRelation();
            this.admin    = new DataInfo(username, user.GetID(username));
        }
        private void Open() {
            if (connection.State != ConnectionState.Open) connection.Open();
        }
        private void Close() {
            if (connection.State != ConnectionState.Closed) connection.Close();
        }
        private User GetUser(String filter, Boolean online) {
            String command = "SELECT Account.AccountID, Account.Username " + 
                             "FROM Account";
            if (online) command += ", General, State " +
                                   "WHERE State.Name <> 'Offline' AND " +
                                   "      State.StateID = General.StateID AND " +
                                   "      General.AccountID = Account.AccountID";
            if (filter.Length > 0) {
                if (online) command += " AND ";
                else command += " WHERE ";
                command += "Account.Username LIKE '%" + filter + "%'";
            }
            SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(command, connection));
            adapter.TableMappings.Add("Table", "Account");
            DataSet dataSet = new DataSet("Account");
            adapter.Fill(dataSet);
            List<DataInfo> list = new List<DataInfo>();
            foreach (DataRow row in dataSet.Tables["Account"].Rows) {
                list.Add(new DataInfo(row["Username"].ToString(), Convert.ToInt32(row["AccountID"])));
            }
            return new User(list);
        }
        private State GetState() {
            String command = "SELECT State.StateID, State.Name " + 
                             "FROM   State";
            SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(command, connection));
            adapter.TableMappings.Add("Table", "State");
            DataSet dataSet = new DataSet("State");
            adapter.Fill(dataSet);
            List<DataInfo> list = new List<DataInfo>();
            foreach (DataRow row in dataSet.Tables["State"].Rows) {
                list.Add(new DataInfo(row["Name"].ToString(), Convert.ToInt32(row["StateID"])));
            }
            return new State(list);
        }
        private Relation GetRelation() {
            String command = "SELECT Relation.RelationID, Relation.Name " + 
                             "FROM   Relation";
            SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(command, connection));
            adapter.TableMappings.Add("Table", "Relation");
            DataSet dataSet = new DataSet("Relation");
            adapter.Fill(dataSet);
            List<DataInfo> list = new List<DataInfo>();
            foreach (DataRow row in dataSet.Tables["Relation"].Rows) {
                list.Add(new DataInfo(row["Name"].ToString(), Convert.ToInt32(row["RelationID"])));
            }
            return new Relation(list);
        }
        private Gender GetGender() {
            String command = "SELECT Gender.GenderID, Gender.Name " + 
                             "FROM   Gender";
            SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(command, connection));
            adapter.TableMappings.Add("Table", "Gender");
            DataSet dataSet = new DataSet("Gender");
            adapter.Fill(dataSet);
            List<DataInfo> list = new List<DataInfo>();
            foreach (DataRow row in dataSet.Tables["Gender"].Rows) {
                list.Add(new DataInfo(row["Name"].ToString(), Convert.ToInt32(row["GenderID"])));
            }
            return new Gender(list);
        }
        private Rank GetRank() {
            String command = "SELECT Rank.RankID, Rank.Name " + 
                             "FROM   Rank";
            SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(command, connection));
            adapter.TableMappings.Add("Table", "Rank");
            DataSet dataSet = new DataSet("Rank");
            adapter.Fill(dataSet);
            List<DataInfo> list = new List<DataInfo>();
            foreach (DataRow row in dataSet.Tables["Rank"].Rows) {
                list.Add(new DataInfo(row["Name"].ToString(), Convert.ToInt32(row["RankID"])));
            }
            return new Rank(list);
        }
        public GeneralInfo GetGeneral(String username) {
            String command = "SELECT Account.Username, Account.Password, General.Name, State.Name As State, General.Status " + 
                             "FROM   Account, General, State " + 
                             "WHERE  Account.AccountID = General.AccountID AND " +
                             "       General.StateID = State.StateID AND " +
                             "       Account.Username = '" + username + "'";
            Open();
            SqlDataReader row = new SqlCommand(command, connection).ExecuteReader();
            GeneralInfo info = (row.HasRows && row.Read()) ? new GeneralInfo(row["Username"].ToString(), row["Password"].ToString(), row["Name"].ToString(), row["State"].ToString(), row["Status"].ToString()) : new GeneralInfo();
            Close();
            return info;
        }
        public PrivateInfo GetPrivate(String username) {
            String command = "SELECT Gender.Name, Private.Birthday, Private.SecurityQuestion, Private.SecurityAnswer " + 
                             "FROM   Account, Private, Gender " + 
                             "WHERE  Account.AccountID = Private.AccountID AND " + 
                             "       Private.GenderID = Gender.GenderID AND " +
                             "       Account.Username = '" + username + "'";
            Open();
            SqlDataReader row = new SqlCommand(command, connection).ExecuteReader();
            PrivateInfo info = new PrivateInfo();
            if (row.HasRows && row.Read()) {
                String friend = "";
	            SqlCommand cmd  = new SqlCommand("SELECT Username, ID FROM GetFriends('" + username + "')", connection);
	            cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                List<DataInfo> list = new List<DataInfo>();
                while (reader.Read()) {
                    list.Add(new DataInfo(reader["Username"].ToString(), Convert.ToInt32(reader["ID"])));
                }
                if (friend.Length > 0) friend = friend.Remove(friend.Length - 2);
                info = new PrivateInfo(row["Name"].ToString(), Convert.ToDateTime(row["Birthday"].ToString()), row["SecurityQuestion"].ToString(), row["SecurityAnswer"].ToString(), list);
            }
            Close();
            return info;
        }
        public CustomInfo GetCustom(String username) {
            String command = "SELECT Rank.Name " + 
                             "FROM   Account, Rank, Private " +  
                             "WHERE  Account.Username = '" + username + "' AND " + 
                             "       Account.AccountID = Private.AccountID AND " + 
                             "       Private.RankID = Rank.RankID";
            Open();
            SqlDataReader row = new SqlCommand(command, connection).ExecuteReader();
            CustomInfo info = (row.HasRows && row.Read()) ? new CustomInfo(row["Name"].ToString(), GetAvatar(username), GetBan(username), GetSilence(username)) : new CustomInfo();
            Close();
            return info;
        }
        private Avatar GetAvatar(String username) {
            String command = "SELECT Avatar.Data " + 
                             "FROM   Account, Avatar " +  
                             "WHERE  Account.Username = '" + username + "' AND " + 
                             "       Account.AccountID = Avatar.AccountID";
            SqlDataReader row = new SqlCommand(command, connection).ExecuteReader();
            return (row.HasRows && row.Read()) ? new Avatar(new MemoryStream((byte[])row["Data"])) : new Avatar();
        }
        private BanInfo GetBan(String username) {
            String command = "SELECT A2.Username, Ban.Expire, Ban.Reason " + 
                             "FROM   Account As A1, Account As A2, Ban " + 
                             "WHERE  A1.AccountID = Ban.AccountID AND " + 
                             "       A2.AccountID = Ban.AdminID AND " +
                             "       A1.Username = '" + username + "'";
            SqlDataReader row = new SqlCommand(command, connection).ExecuteReader();
            return (row.HasRows && row.Read()) ? new BanInfo(row["Username"].ToString(), Convert.ToDateTime(row["Expire"].ToString()), row["Reason"].ToString()) : new BanInfo();
        }
        private SilenceInfo GetSilence(String username) {
            String command = "SELECT A2.Username, Silence.Expire, Silence.Reason " + 
                             "FROM   Account As A1, Account As A2, Silence " + 
                             "WHERE  A1.AccountID = Silence.AccountID AND " + 
                             "       A2.AccountID = Silence.AdminID AND " +
                             "       A1.Username = '" + username + "'";
            SqlDataReader row = new SqlCommand(command, connection).ExecuteReader();
            return (row.HasRows && row.Read()) ? new SilenceInfo(row["Username"].ToString(), Convert.ToDateTime(row["Expire"].ToString()), row["Reason"].ToString()) : new SilenceInfo();
        }
        public void SetGeneral(String username, GeneralInfo info) {
            try {
                Open();
	            SqlCommand command  = new SqlCommand("SetGeneral", connection);
	            command.CommandType = CommandType.StoredProcedure;
	            command.Parameters.Add(new SqlParameter("@Admin", this.admin.Name));
	            command.Parameters.Add(new SqlParameter("@Owner", username));
	            command.Parameters.Add(new SqlParameter("@Username", info.Username));
	            command.Parameters.Add(new SqlParameter("@Password", info.Password));
	            command.Parameters.Add(new SqlParameter("@Name", info.Name));
	            command.Parameters.Add(new SqlParameter("@State", info.State));
	            command.Parameters.Add(new SqlParameter("@Status", info.Status));
                SqlParameter error = command.Parameters.Add(new SqlParameter("@Error", SqlDbType.VarChar, 128));
                error.Direction = ParameterDirection.Output;
                command.ExecuteNonQuery();
                if (((String)error.Value).Length > 0) throw new Exception((String)error.Value);
                if (this.admin.Name == username) this.admin = new DataInfo(info.Username, this.admin.ID);
            } catch (Exception e) {
                throw e;
            } finally {
                Close();
            }
        }
        public void SetPrivate(String username, PrivateInfo info) {
            try {
                //System.Windows.Forms.MessageBox.Show(info.Birthday.ToString("yyyy/MM/dd"));
                Open();
	            {
                    SqlCommand command = new SqlCommand("SetPrivate", connection);
	                command.CommandType = CommandType.StoredProcedure;
	                command.Parameters.Add(new SqlParameter("@Admin", this.admin.Name));
	                command.Parameters.Add(new SqlParameter("@Owner", username));
	                command.Parameters.Add(new SqlParameter("@Gender", info.Gender));
	                command.Parameters.Add(new SqlParameter("@Birthday", info.Birthday.ToString("yyyy/MM/dd")));
	                command.Parameters.Add(new SqlParameter("@Question", info.Question));
	                command.Parameters.Add(new SqlParameter("@Answer", info.Answer));
                    SqlParameter error = command.Parameters.Add(new SqlParameter("@Error", SqlDbType.VarChar, 128));
                    error.Direction = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                    if (((String)error.Value).Length > 0) throw new Exception((String)error.Value);
                }
                {
                    SqlCommand command  = new SqlCommand("SELECT Username, ID FROM GetFriends('" + username + "')", connection);
                    command.CommandType = CommandType.Text;
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read()) {
                        String name = reader["Username"].ToString();
                        if (!info.Friend.Exists(item => item.Name == name)) {   
                            command = new SqlCommand("DeleteFriend", connection);
	                        command.CommandType = CommandType.StoredProcedure;
	                        command.Parameters.Add(new SqlParameter("@Admin", this.admin.Name));
	                        command.Parameters.Add(new SqlParameter("@Owner", username));
	                        command.Parameters.Add(new SqlParameter("@Friend", name));
                            SqlParameter error = command.Parameters.Add(new SqlParameter("@Error", SqlDbType.VarChar, 128));
                            error.Direction = ParameterDirection.Output;
                            command.ExecuteNonQuery();
                            if (((String)error.Value).Length > 0) throw new Exception((String)error.Value);
                        }
                    }
                }
                {
                    foreach (DataInfo friend in info.Friend) {
                        SqlCommand command = new SqlCommand("AddFriend", connection);
	                    command.CommandType = CommandType.StoredProcedure;
	                    command.Parameters.Add(new SqlParameter("@Admin", this.admin.Name));
	                    command.Parameters.Add(new SqlParameter("@Owner", username));
	                    command.Parameters.Add(new SqlParameter("@Friend", friend.Name));
	                    command.Parameters.Add(new SqlParameter("@Relation", this.Relation.GetRelation(friend.ID)));
                        SqlParameter error = command.Parameters.Add(new SqlParameter("@Error", SqlDbType.VarChar, 128));
                        error.Direction = ParameterDirection.Output;
                        command.ExecuteNonQuery();
                        if (((String)error.Value).Length > 0) throw new Exception((String)error.Value);
                    }
                }
            } catch (Exception e) {
                throw e;
            } finally {
                Close();
            }
        }
        public void SetCustom(String username, CustomInfo info) {
            try {
                //System.Windows.Forms.MessageBox.Show(info.Ban.Expire.ToString("yyyy/MM/dd") + 
                //                                      Environment.NewLine + 
                //                                      info.Silence.Expire.ToString("yyyy/MM/dd"));
                Open();
	            {
                    SqlCommand command = new SqlCommand("SetCustom", connection);
	                command.CommandType = CommandType.StoredProcedure;
	                command.Parameters.Add(new SqlParameter("@Admin", this.admin.Name));
	                command.Parameters.Add(new SqlParameter("@Owner", username));
	                command.Parameters.Add(new SqlParameter("@Rank", info.Rank));
	                command.Parameters.Add(new SqlParameter("@AvatarHash", info.Avatar.Hash));
                    command.Parameters.Add(new SqlParameter("@AvatarData", (Object)info.Avatar.Data));
                    SqlParameter error = command.Parameters.Add(new SqlParameter("@Error", SqlDbType.VarChar, 128));
                    error.Direction = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                    if (((String)error.Value).Length > 0) throw new Exception((String)error.Value);
                }
	            if (info.Ban.Admin.Length > 0) {
                    SqlCommand command = new SqlCommand("SetBan", connection);
	                command.CommandType = CommandType.StoredProcedure;
	                command.Parameters.Add(new SqlParameter("@Admin", this.admin.Name));
	                command.Parameters.Add(new SqlParameter("@Owner", username));
	                command.Parameters.Add(new SqlParameter("@Expire", info.Ban.Expire.ToString("yyyy/MM/dd")));
	                command.Parameters.Add(new SqlParameter("@Reason", info.Ban.Reason));
                    SqlParameter error = command.Parameters.Add(new SqlParameter("@Error", SqlDbType.VarChar, 128));
                    error.Direction = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                    if (((String)error.Value).Length > 0) throw new Exception((String)error.Value);
                } else {
                    SqlCommand command = new SqlCommand("RemoveBan", connection);
	                command.CommandType = CommandType.StoredProcedure;
	                command.Parameters.Add(new SqlParameter("@Admin", this.admin.Name));
	                command.Parameters.Add(new SqlParameter("@Owner", username));
                    SqlParameter error = command.Parameters.Add(new SqlParameter("@Error", SqlDbType.VarChar, 128));
                    error.Direction = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                    if (((String)error.Value).Length > 0) throw new Exception((String)error.Value);
                }
                if (info.Silence.Admin.Length > 0) {
                    SqlCommand command = new SqlCommand("SetSilence", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@Admin", this.admin.Name));
                    command.Parameters.Add(new SqlParameter("@Owner", username));
                    command.Parameters.Add(new SqlParameter("@Expire", info.Silence.Expire.ToString("yyyy/MM/dd")));
                    command.Parameters.Add(new SqlParameter("@Reason", info.Silence.Reason));
                    SqlParameter error = command.Parameters.Add(new SqlParameter("@Error", SqlDbType.VarChar, 128));
                    error.Direction = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                    if (((String)error.Value).Length > 0) throw new Exception((String)error.Value);
                } else {
                    SqlCommand command = new SqlCommand("RemoveSilence", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@Admin", this.admin.Name));
                    command.Parameters.Add(new SqlParameter("@Owner", username));
                    SqlParameter error = command.Parameters.Add(new SqlParameter("@Error", SqlDbType.VarChar, 128));
                    error.Direction = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                    if (((String)error.Value).Length > 0) throw new Exception((String)error.Value);
                }
            } catch (Exception e) {
                throw e;
            } finally {
                Close();
            }
        }
        public Int32 GetFeedbackCount() {
            String command = "SELECT COUNT(Feedback.AccountID) As 'Count' " + 
                             "FROM   Feedback";
            Open();
            SqlDataReader row = new SqlCommand(command, connection).ExecuteReader();
            Int32 count = (row.HasRows && row.Read()) ? Convert.ToInt32(row["Count"]) : 0;
            Close();
            return count;
        }
        public void GetReport(Boolean rank, Boolean gender, Boolean state, Boolean ban, Boolean silence) {   
            List<Report.Title> title = new List<Report.Title>();
            String command = "SELECT COUNT(Account.AccountID) As 'Count' " + 
                             "FROM   Account";
            Open();
            SqlDataReader row = new SqlCommand(command, connection).ExecuteReader();
            Int32 count = (row.HasRows && row.Read()) ? Convert.ToInt32(row["Count"]) : 0;
            Close();
            if (rank) title.Add(new Report.Title("Rank", GetReportByRank(count)));
            if (gender) title.Add(new Report.Title("Gender", GetReportByGender(count)));
            if (state) title.Add(new Report.Title("State", GetReportByState(count)));
            if (ban) title.Add(new Report.Title("Ban", GetReportByBan(count)));
            if (silence) title.Add(new Report.Title("Silence", GetReportBySilence(count)));
            title.Add(new Report.Title("Users with the most active relations", GetReportByRelation(count)));
            title.Add(new Report.Title("Blacklist", GetReportByBlacklist()));
            Report report = new Report(title);
        }
        public void GetFeedback() {   
            List<Report.Title> title = new List<Report.Title>();
            title.Add(new Report.Title("Feedbacks", GetReportByFeedback()));
            Report report = new Report(title);
        }
        public void AddBlacklist(String address, String reason) {
            String command = "INSERT INTO Blacklist " + 
                             "VALUES ('" + address + "', '" + reason + "')";
            Open();
            new SqlCommand(command, connection).ExecuteNonQuery();
            Close();
        }
        public void EditBlacklist(String address, String reason) {
            String command = "UPDATE Blacklist " + 
                             "SET    Blacklist.Reason = '" + reason + "' " + 
                             "WHERE  Blacklist.IPAddress = '" + address + "'";
            Open();
            new SqlCommand(command, connection).ExecuteNonQuery();
            Close();
        }
        public void RemoveBlacklist(String address) {
            String command = "DELETE FROM Blacklist " + 
                             "WHERE  Blacklist.IPAddress = '" + address + "'";
            Open();
            new SqlCommand(command, connection).ExecuteNonQuery();
            Close();
        }
        public Boolean GetBlacklistAddress(String address) {
            if (!IsIPAddress(address)) throw new Exception("\"" + address + "\" is not a valid ip address!");
            String command = "SELECT Blacklist.IPAddress " + 
                             "FROM   Blacklist " + 
                             "WHERE  Blacklist.IPAddress = '" + address + "'";
            Open();
            Boolean value = new SqlCommand(command, connection).ExecuteReader().HasRows;
            Close();
            return value;
        }
        private Boolean IsIPAddress(String address) {
            return Regex.Match(address, "^\\b(?:\\d{1,3}\\.){3}\\d{1,3}\\b$").Success;
        }
        public Boolean IsUserAllowed(String username) {
            try {
                Open();
	            SqlCommand command  = new SqlCommand("IsUserAllowed", connection);
	            command.CommandType = CommandType.StoredProcedure;
	            command.Parameters.Add(new SqlParameter("@Username", username));
                SqlParameter error = command.Parameters.Add(new SqlParameter("@Error", SqlDbType.Int));
                error.Direction = ParameterDirection.ReturnValue;
                command.ExecuteNonQuery();
                return (Boolean)((Int32)error.Value == 1);
            } catch (Exception e) { } finally {
                Close();
            }
            return false;
        }
        private List<Report.Column> GetReportByRank(Int32 count) {
            List<Report.Column> list = new List<Report.Column>();
            String command = "SELECT Rank.Name, COUNT(Private.RankID) As 'Count'" + 
                             "FROM   Rank LEFT OUTER JOIN Private ON Rank.RankID = Private.RankID " + 
                             "GROUP BY Rank.Name, Rank.RankID " + 
                             "ORDER BY Rank.RankID ASC";
            SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(command, connection));
            adapter.TableMappings.Add("Table", "Rank");
            DataSet dataSet = new DataSet("Rank");
            adapter.Fill(dataSet);
            foreach (DataRow row in dataSet.Tables["Rank"].Rows) {
                Int32 number = Convert.ToInt32(row["Count"]);
                list.Add(new Report.Column(row["Name"].ToString(), number.ToString() + " member" + ((number > 1) ? "s" : " ") + " (" + number * 100.0f / count + "%)"));
            }
            return list;
        }
        private List<Report.Column> GetReportByGender(Int32 count) {
            List<Report.Column> list = new List<Report.Column>();
            String command = "SELECT Gender.Name, COUNT(Private.GenderID) As 'Count'" + 
                             "FROM   Gender LEFT OUTER JOIN Private ON Gender.GenderID = Private.GenderID " + 
                             "GROUP BY Gender.Name, Gender.GenderID " + 
                             "ORDER BY Gender.GenderID ASC";
            SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(command, connection));
            adapter.TableMappings.Add("Table", "Gender");
            DataSet dataSet = new DataSet("Gender");
            adapter.Fill(dataSet);
            foreach (DataRow row in dataSet.Tables["Gender"].Rows) {
                Int32 number = Convert.ToInt32(row["Count"]);
                list.Add(new Report.Column(row["Name"].ToString(), number.ToString() + " member" + ((number > 1) ? "s" : " ") + " (" + number * 100.0f / count + "%)"));
            }
            return list;
        }
        private List<Report.Column> GetReportByState(Int32 count) {
            List<Report.Column> list = new List<Report.Column>();
            String command = "SELECT State.Name, COUNT(General.StateID) As 'Count'" + 
                             "FROM   State LEFT OUTER JOIN General ON State.StateID = General.StateID " + 
                             "GROUP BY State.Name, State.StateID " + 
                             "ORDER BY State.StateID ASC";
            SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(command, connection));
            adapter.TableMappings.Add("Table", "State");
            DataSet dataSet = new DataSet("State");
            adapter.Fill(dataSet);
            foreach (DataRow row in dataSet.Tables["State"].Rows) {
                Int32 number = Convert.ToInt32(row["Count"]);
                list.Add(new Report.Column(row["Name"].ToString(), number.ToString() + " member" + ((number > 1) ? "s" : " ") + " (" + number * 100.0f / count + "%)"));
            }
            return list;
        }
        private List<Report.Column> GetReportByBan(Int32 count) {
            List<Report.Column> list = new List<Report.Column>();
            String command = "SELECT Account.Username, Rank.Name, COUNT(Ban.AccountID) As 'Count' " +
                             "FROM   Account LEFT OUTER JOIN Ban ON Account.AccountID = Ban.AdminID " +
			                 "               LEFT OUTER JOIN Private ON Account.AccountID = Private.AccountID " +
			                 "               LEFT OUTER JOIN Rank ON Private.RankID = Rank.RankID " +
                             "WHERE Rank.Name = 'Administrator' OR Rank.Name = 'Moderator' " +
                             "GROUP BY Account.Username, Rank.Name, Rank.RankID " +
                             "ORDER BY Rank.RankID ASC";
            SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(command, connection));
            adapter.TableMappings.Add("Table", "Admin");
            DataSet dataSet = new DataSet("Admin");
            adapter.Fill(dataSet);
            foreach (DataRow row in dataSet.Tables["Admin"].Rows) {
                Int32 number = Convert.ToInt32(row["Count"]);
                list.Add(new Report.Column(row["Username"].ToString(), number.ToString() + " member" + ((number > 1) ? "s" : " ") + " (" + number * 100.0f / count + "%)"));
            }
            return list;
        }
        private List<Report.Column> GetReportBySilence(Int32 count) {
            List<Report.Column> list = new List<Report.Column>();
            String command = "SELECT Account.Username, Rank.Name, COUNT(Silence.AccountID) As 'Count' " +
                             "FROM   Account LEFT OUTER JOIN Silence ON Account.AccountID = Silence.AdminID " +
			                 "               LEFT OUTER JOIN Private ON Account.AccountID = Private.AccountID " +
			                 "               LEFT OUTER JOIN Rank ON Private.RankID = Rank.RankID " +
                             "WHERE Rank.Name = 'Administrator' OR Rank.Name = 'Moderator' " +
                             "GROUP BY Account.Username, Rank.Name, Rank.RankID " +
                             "ORDER BY Rank.RankID ASC";
            SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(command, connection));
            adapter.TableMappings.Add("Table", "Admin");
            DataSet dataSet = new DataSet("Admin");
            adapter.Fill(dataSet);
            foreach (DataRow row in dataSet.Tables["Admin"].Rows) {
                Int32 number = Convert.ToInt32(row["Count"]);
                list.Add(new Report.Column(row["Username"].ToString(), number.ToString() + " member" + ((number > 1) ? "s" : " ") + " (" + number * 100.0f / count + "%)"));
            }
            return list;
        }
        private List<Report.Column> GetReportByRelation(Int32 count) {
            List<Report.Column> list = new List<Report.Column>();
            String command = "SELECT TOP 3 Account.Username, COUNT(Account.AccountID) As 'Count' " + 
                             "FROM   Account LEFT OUTER JOIN AccountRelation ON (Account.AccountID = AccountRelation.AccountID OR Account.AccountID = AccountRelation.TargetID) " + 
                             "               LEFT OUTER JOIN Relation ON AccountRelation.RelationID = Relation.RelationID " + 
                             "WHERE  Relation.Name <> 'Pending' " + 
                             "GROUP BY Account.Username, Account.AccountID " + 
                             "ORDER BY COUNT(Account.AccountID) DESC";
            SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(command, connection));
            adapter.TableMappings.Add("Table", "Relation");
            DataSet dataSet = new DataSet("Relation");
            adapter.Fill(dataSet);
            foreach (DataRow row in dataSet.Tables["Relation"].Rows) {
                Int32 number = Convert.ToInt32(row["Count"]);
                list.Add(new Report.Column(row["Username"].ToString(), number.ToString() + " active relation" + ((number > 1) ? "s" : " ") + " (" + number * 100.0f / count + "%)"));
            }
            return list;
        }
        private List<Report.Column> GetReportByFeedback() {
            List<Report.Column> list = new List<Report.Column>();
            String command = "SELECT Account.Username, Feedback.Feedback " + 
                             "FROM   Account LEFT OUTER JOIN Feedback ON Account.AccountID = Feedback.AccountID";
            SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(command, connection));
            adapter.TableMappings.Add("Table", "Feedback");
            DataSet dataSet = new DataSet("Feedback");
            adapter.Fill(dataSet);
            foreach (DataRow row in dataSet.Tables["Feedback"].Rows) {
                list.Add(new Report.Column(row["Username"].ToString(), row["Feedback"].ToString()));
            }
            return list;
        }
        private List<Report.Column> GetReportByBlacklist() {
            List<Report.Column> list = new List<Report.Column>();
            String command = "SELECT Blacklist.IPAddress, Blacklist.Reason " +
                             "FROM   Blacklist";
            SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(command, connection));
            adapter.TableMappings.Add("Table", "Blacklist");
            DataSet dataSet = new DataSet("Blacklist");
            adapter.Fill(dataSet);
            if (dataSet.Tables["Blacklist"].Rows.Count == 0) list.Add(new Report.Column("", "There are no blacklisted IP addresses!"));
            foreach (DataRow row in dataSet.Tables["Blacklist"].Rows) {
                list.Add(new Report.Column(row["IPAddress"].ToString(), row["Reason"].ToString()));
            }
            return list;
        }
        public State State {
            get { return state; }
        }
        public Gender Gender {
            get { return gender; }
        }
        public Rank Rank {
            get { return rank; }
        }
        public Relation Relation {
            get { return relation; }
        }
        public User User(String filter, Boolean online) {
            user = GetUser(filter, online);
            return user;
        }
    }
    public class Info { }
    public class GeneralInfo : Info {
        private String username;
        private String password;
        private String name;
        private String state;
        private String status;
        public GeneralInfo() {
            username = password = name = state = status = "";
        }
        public GeneralInfo(String username, String password, String name, String state, String status) {
            this.username = username;
            this.password = password;
            this.name = name;
            this.state = state;
            this.status = status;
        }
        public String Username {
           get { return username; }
        }
        public String Password {
           get { return password; }
        }
        public String Name {
           get { return name; }
        }
        public String State {
           get { return state; }
        }
        public String Status {
           get { return status; }
        }
        public override String ToString() {
            String data = "Account.Username = '" + username + "'\nAccount.Password = '" + password + "'\nGeneral.Name = '" + name + "'\nGeneral.State = '" + state + "'\nGeneral.Status = '" + status + "'";
            return data.Replace("'", "''");
        }
    }
    public class PrivateInfo : Info {
        private String gender;
        private DateTime birthday;
        private String question;
        private String answer;
        private List<DataInfo> list;
        public PrivateInfo() {
            gender = question = answer = "";
            birthday = DateTime.Now;
            list = new List<DataInfo>();
        }
        public PrivateInfo(String gender, DateTime birthday, String question, String answer, List<DataInfo> list) {
            this.gender = gender;
            this.birthday = birthday;
            this.question = question;
            this.answer = answer;
            this.list = list;
        }
        public String Gender{
           get { return gender; }
        }
        public DateTime Birthday {
           get { return birthday; }
        }
        public String Question {
           get { return question; }
        }
        public String Answer {
           get { return answer; }
        }
        public List<DataInfo> Friend {
           get { return list; }
        }
        public override String ToString() {
            String data = "Private.Gender = '" + gender + "'\nPrivate.Birthday = '" + birthday + "'\nPrivate.SecurityQuestion = '" + question + "'\nPrivate.SecurityAnswer = '" + answer + "'";
            return data.Replace("'", "''");
        }
    }
    public class BanInfo : Info {
        private String admin;
        private DateTime expire;
        private String reason;
        public BanInfo() {
            admin = reason = "";
            expire = DateTime.Now;
        }
        public BanInfo(String admin, DateTime expire, String reason) {
            this.admin = admin;
            this.expire = expire;
            this.reason = reason;
        }
        public String Admin {
           get { return admin; }
        }
        public DateTime Expire {
           get { return expire; }
        }
        public String Reason {
           get { return reason; }
        }
    }
    public class SilenceInfo : Info {
        private String admin;
        private DateTime expire;
        private String reason;
        public SilenceInfo() {
            admin = reason = "";
            expire = DateTime.Now;
        }
        public SilenceInfo(String admin, DateTime expire, String reason) {
            this.admin = admin;
            this.expire = expire;
            this.reason = reason;
        }
        public String Admin {
           get { return admin; }
        }
        public DateTime Expire {
           get { return expire; }
        }
        public String Reason {
           get { return reason; }
        }
    }
    public class CustomInfo : Info {
        private String rank;
        private Avatar avatar;
        private BanInfo ban;
        private SilenceInfo silence;
        public CustomInfo() {
            rank = "";
            avatar = new Avatar();
            ban = new BanInfo();
            silence = new SilenceInfo();
        }
        public CustomInfo(String rank, Avatar avatar, BanInfo ban, SilenceInfo silence) {
            this.rank = rank;
            this.avatar = avatar;
            this.ban = ban;
            this.silence = silence;
        }
        public String Rank {
           get { return rank; }
        }
        public Avatar Avatar {
           get { return avatar; }
        }
        public BanInfo Ban {
           get { return ban; }
        }
        public SilenceInfo Silence {
           get { return silence; }
        }
    }
}
